﻿// Tierless
// Copyright © 2010 Alphora
// This file is licensed under a modified BSD-license (see tierless_license.txt).

using System;
using System.Configuration;
using System.Data;
using System.Data.Common;
using System.Transactions;
using System.Linq;

using IsolationLevel = System.Data.IsolationLevel;

namespace Tierless.Infrastructure.Data
{
	/// <summary>
	/// Provides a connection management wrapper for ADO.NET.
	/// </summary>
	public class Database
	{
		public const string CDefaultConnectionSettingsName = "Default";
		public const string CDefaultConnectionString = "Data Source=.;Initial Catalog=Database;Integrated Security=True";
		public const string CDefaultProviderName = "System.Data.SqlClient";
		
		private static Database FDefault;
		/// <summary>
		/// Gets the default static Database instance.
		/// </summary>
		public static Database Default
		{
			get { return FDefault ?? (FDefault = new Database()); }
		}

		/// <summary>
		/// Constructs a new Database instance using the given named connection string.
		/// </summary>
		/// <param name="AConnectionName">The name of the connection string to use to connect to the underlying database.</param>
		public Database(string AConnectionName)
		{
			if (String.IsNullOrEmpty(AConnectionName)) throw new ArgumentNullException("AConnectionName");
			
			ConnectionName = AConnectionName;
		}

		/// <summary>
		/// Constructs a new Database instuance using the default named connection string.
		/// </summary>
		public Database() : this(CDefaultConnectionSettingsName) { }

		/// <summary>
		/// Gets the name of the connection for this database.
		/// </summary>
		public string ConnectionName { get; private set; }

		private ConnectionStringSettings FConnectionSettings;
		protected ConnectionStringSettings ConnectionSettings
		{
			get
			{
				if (FConnectionSettings == null)
				{
					var LConnectionStrings = ConfigurationManager.ConnectionStrings;
					if (LConnectionStrings != null)
						FConnectionSettings = LConnectionStrings[ConnectionName];
						
					if (FConnectionSettings == null)
					{
						if (ConnectionName == CDefaultConnectionSettingsName)
						{
							FConnectionSettings = 
								new ConnectionStringSettings
								(
									CDefaultConnectionSettingsName,
									CDefaultConnectionString,
									CDefaultProviderName 
								);
						}
						else
							throw new InvalidOperationException(String.Format("Could not find a connection settings element named '{0}' in the application configuration.", ConnectionName));
					}
				}
				
				return FConnectionSettings;
			}
		}
		
		/// <summary>
		/// Gets the connection string.
		/// </summary>
		public string ConnectionString
		{
			get
			{
				return ConnectionSettings.ConnectionString;
			}
		}
		
		private int? FCommandTimeout;
		/// <summary>
		/// Gets or sets the command timeout, in seconds.
		/// </summary>
		/// <value>The command timeout, in seconds.</value>
		/// <remarks>
		/// The default for this setting is null, which means the underlying ADO.NET command will have the default command timeout of 30 seconds.
		/// If this value is specified, the underlying command uses this value.
		/// The timeout must be a value greater than or equal to 0, with 0 indicating an indefinite timeout.
		/// </remarks>
		public int? CommandTimeout
		{
			get { return FCommandTimeout; }
			set { FCommandTimeout = value; }
		}
		
		private DbProviderFactory FProviderFactory;
		protected DbProviderFactory ProviderFactory
		{
			get { return FProviderFactory ?? (FProviderFactory = DbProviderFactories.GetFactory(ConnectionSettings.ProviderName)); }
		}
		
		protected internal DbConnection CreateConnection()
		{
			var LConnection = ProviderFactory.CreateConnection();
			LConnection.ConnectionString = ConnectionSettings.ConnectionString;
			LConnection.Open();
			return LConnection;
		}
		
		protected ConnectionWrapper GetConnection()
		{
			return GetConnection(true);
		}
		
		protected ConnectionWrapper GetConnection(bool AShouldDispose)
		{
			var LConnection = TransactionScopeConnections.GetConnection(this);

			return LConnection != null ? new ConnectionWrapper(LConnection, false) : new ConnectionWrapper(CreateConnection(), AShouldDispose);
		}

		protected virtual DbType TypeToDbType(Type AType)
		{
			switch (AType.Name)
			{
				case "String" : return DbType.AnsiString;
				case "Byte" : return DbType.Byte;
				case "Int16" : return DbType.Int16;
				case "Int32" : return DbType.Int32;
				case "Int64" : return DbType.Int64;
				case "Boolean" : return DbType.Boolean;
				case "Decimal" : return DbType.Decimal;
				case "DateTime" : return DbType.DateTime;
				case "Double" : return DbType.Double;
				case "Guid" : return DbType.Guid;
				case "Byte[]" : return DbType.Binary;
				default : throw new ArgumentException("Unable to determine DbType for Type '{0}'.", AType.Name);
			}
		}

		protected virtual DbParameter ParamToParameter(Param AParam)
		{
			var LParameter = ProviderFactory.CreateParameter();
			LParameter.ParameterName = AParam.Name;
			if (AParam.Type != null)
				LParameter.DbType = TypeToDbType(AParam.Type);
			LParameter.Direction = AParam.Modifier;
			if ((LParameter.Direction == ParameterDirection.Input) || (LParameter.Direction == ParameterDirection.InputOutput))
				LParameter.Value = AParam.Value ?? DBNull.Value;
			if ((LParameter.Direction == ParameterDirection.Output) || (LParameter.Direction == ParameterDirection.InputOutput))
			{
				if ((LParameter.DbType == DbType.AnsiString) && (LParameter.Size == 0))
					LParameter.Size = 8000; // NOTE: This is assuming MSSQL types
				if (LParameter.DbType == DbType.Decimal)
				{
					IDbDataParameter LDbParameter = LParameter;
					if ((LDbParameter != null) && (LDbParameter.Precision == 0) && (LDbParameter.Scale == 0))
					{
						LDbParameter.Precision = 38; // NOTE: This is assuming MSSQL types
						LDbParameter.Scale = 8;
					}
				}
			}
				
			return LParameter;
		}

		protected virtual void SetOutputParameters(DbCommand ACommand, Param[] AParams)
		{
			if (AParams != null)
				for (var LIndex = 0; LIndex < AParams.Length; LIndex++)
					if ((AParams[LIndex].Modifier == ParameterDirection.InputOutput) || (AParams[LIndex].Modifier == ParameterDirection.Output))
						AParams[LIndex].Value = ACommand.Parameters[LIndex].Value;
		} 

		protected virtual DbCommand CreateCommand(DbConnection AConnection, TransactionContext ATransaction, CommandType ACommandType, string AStatement, Param[] AParams)
		{
			var LCommand = ProviderFactory.CreateCommand();
			LCommand.Connection = AConnection;
			if (FCommandTimeout != null)
				LCommand.CommandTimeout = FCommandTimeout.Value;
			if (ATransaction != null)
				LCommand.Transaction = ATransaction.Transaction;
			LCommand.CommandText = AStatement;
			LCommand.CommandType = ACommandType;
			if (AParams != null)
				foreach (var LParam in AParams)
					LCommand.Parameters.Add(ParamToParameter(LParam));

			return LCommand;
		}

		protected internal DbDataAdapter GetDataAdapter(string ATableName)
		{
			var LDataAdapter = ProviderFactory.CreateDataAdapter();
			using (var LWrapper = GetConnection())
			{
				LDataAdapter.SelectCommand = CreateCommand(LWrapper.Connection, null, CommandType.Text, String.Format("select * from [{0}]", ATableName), null);
				var LCommandBuilder = ProviderFactory.CreateCommandBuilder();
				LCommandBuilder.DataAdapter = LDataAdapter;
				LDataAdapter.InsertCommand = LCommandBuilder.GetInsertCommand(true);
				LDataAdapter.UpdateCommand = LCommandBuilder.GetUpdateCommand(true);
				LDataAdapter.DeleteCommand = LCommandBuilder.GetDeleteCommand(true);
				return LDataAdapter;
			}
		}
		
		protected void AssociateCommand(DbCommand ACommand, TransactionContext ATransaction, DbConnection AConnection)
		{
			ACommand.Connection = AConnection;
			if (ATransaction != null)
				ACommand.Transaction = ATransaction.Transaction;
		}

		protected internal void LoadDataSetSchema(TransactionContext ATransaction, string ATableName, DbDataAdapter ADataAdapter, DataSet ADataSet)
		{
			using (var LWrapper = (ATransaction == null) ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
			{
				AssociateCommand(ADataAdapter.SelectCommand, ATransaction, LWrapper.Connection);
				ADataAdapter.FillSchema(ADataSet, SchemaType.Source);
			}
		}

		protected internal void LoadDataSet(TransactionContext ATransaction, string ATableName, DbDataAdapter ADataAdapter, DataSet ADataSet)
		{
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = (ATransaction == null) ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
					{
						AssociateCommand(ADataAdapter.SelectCommand, ATransaction, LWrapper.Connection);
						ADataAdapter.Fill(ADataSet, ATableName);
					}
				}
			);
		}

		protected internal void SaveDataSet(TransactionContext ATransaction, string ATableName, DbDataAdapter ADataAdapter, DataSet ADataSet)
		{
			var LTransaction = ATransaction ?? BeginTransaction();
			try
			{
				AssociateCommand(ADataAdapter.InsertCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.UpdateCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.DeleteCommand, LTransaction, LTransaction.Connection);
				ADataAdapter.Update(ADataSet, ATableName);
				if (ATransaction == null)
					LTransaction.Commit();
			}
			finally
			{
				if (ATransaction == null)
					LTransaction.Dispose();
			}
		}
		
		protected internal void SaveDataTable(TransactionContext ATransaction, DbDataAdapter ADataAdapter, DataTable ADataTable)
		{
			var LTransaction = ATransaction ?? BeginTransaction();
			try
			{
				AssociateCommand(ADataAdapter.InsertCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.UpdateCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.DeleteCommand, LTransaction, LTransaction.Connection);
				ADataAdapter.Update(ADataTable);
				if (ATransaction == null)
					LTransaction.Commit();
			}
			finally
			{
				if (ATransaction == null)
					LTransaction.Dispose();
			}
		}
		
		protected internal void SaveDataRows(TransactionContext ATransaction, DbDataAdapter ADataAdapter, DataRow[] ADataRows)
		{
			var LTransaction = ATransaction ?? BeginTransaction();
			try
			{
				AssociateCommand(ADataAdapter.InsertCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.UpdateCommand, LTransaction, LTransaction.Connection);
				AssociateCommand(ADataAdapter.DeleteCommand, LTransaction, LTransaction.Connection);
				ADataAdapter.Update(ADataRows);
				if (ATransaction == null)
					LTransaction.Commit();
			}
			finally
			{
				if (ATransaction == null)
					LTransaction.Dispose();
			}
		}

		/// <summary>
		/// Begins a transaction against the database.
		/// </summary>
		/// <param name="AIsolationLevel">The isolation level for the transaction.</param>
		/// <returns>The transaction context for the transaction.</returns>
		public TransactionContext BeginTransaction(IsolationLevel AIsolationLevel)
		{
			var LWrapper = GetConnection();
			return new TransactionContext(LWrapper, LWrapper.Connection.BeginTransaction(AIsolationLevel));
		}

		/// <summary>
		/// Begins a transactions with the default isolation level.
		/// </summary>
		/// <returns>The transaction context for the transaction.</returns>
		public TransactionContext BeginTransaction()
		{
			var LWrapper = GetConnection();
			return new TransactionContext(LWrapper, LWrapper.Connection.BeginTransaction());
		}

		/// <summary>
		/// Evaluates the given command, returning the scalar result.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the evaluation.</param>
		/// <param name="ACommandType">The type of command to be evaluated.</param>
		/// <param name="ACommandText">The text of the command to be evaluated.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The scalar result of evaluating the command.</returns>
		public object Evaluate(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			object LResult = null;
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = ATransaction == null ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
					{
						var LCommand = CreateCommand(LWrapper.Connection, ATransaction, ACommandType, ACommandText, AParams);
						LResult = LCommand.ExecuteScalar();
						SetOutputParameters(LCommand, AParams);
					}
				}
			);
			return LResult;
		}
		
		/// <summary>
		/// Evaluates the given command, returning the scalar result.
		/// </summary>
		/// <param name="ACommandType">The type of command to be evaluated.</param>
		/// <param name="ACommandText">The text of the command to be evaluated.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The scalar result of evaluating the command.</returns>
		public object Evaluate(CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			return Evaluate(null, ACommandType, ACommandText, AParams);
		}
		
		/// <summary>
		/// Evaluates the given text command, returning the scalar result.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the evaluation.</param>
		/// <param name="AStatement">The text of the command to be evaluated.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The scalar result of evaluating the command.</returns>
		public object Evaluate(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			return Evaluate(ATransaction, CommandType.Text, AStatement, AParams);
		}
		
		/// <summary>
		/// Evaluates the given text command, returning the scalar result.
		/// </summary>
		/// <param name="AStatement">The text of the command to be evaluated.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The scalar result of evaluating the command.</returns>
		public object Evaluate(string AStatement, params Param[] AParams)
		{
			return Evaluate(null, AStatement, AParams);
		}
		
		protected void HandleRetry(Action AAction)
		{
			while (true)
			{
				try
				{
					AAction();
					return;
				}
				catch (System.Data.SqlClient.SqlException LException)
				{
					// Microsoft specifically prescribes that execution be retried for the following errors 
					switch (LException.Number)
					{
						case 40501:	// The service is currently busy.
							System.Threading.Thread.Sleep(10000);	// MS prescribes waiting 10 seconds then retrying
							break;	// try again
						case 40197:	// The service has encountered an error processing your request.
						case 10053:	// A transport-level error has occurred when receiving results from the server. 
						case 10054:	// A transport-level error has occurred when sending the request to the server.
							break;	// try again
						default:
							throw;
					}
				}
			}
		}

		/// <summary>
		/// Executes the given command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for execution.</param>
		/// <param name="ACommandType">The type of command to be executed.</param>
		/// <param name="ACommandText">The text of the command to be executed.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The number of rows affected by the command.</returns>
		public int Execute(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			var LResult = 0;
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = ATransaction == null ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
					{
						var LCommand = CreateCommand(LWrapper.Connection, ATransaction, ACommandType, ACommandText, AParams);
						LResult = LCommand.ExecuteNonQuery();
						SetOutputParameters(LCommand, AParams);
					}
				}
			);
			return LResult;
		}
		
		/// <summary>
		/// Executes the given command.
		/// </summary>
		/// <param name="ACommandType">The type of command to be executed.</param>
		/// <param name="ACommandText">The text of the command to be executed.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The number of rows affected by the command.</returns>
		public int Execute(CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			return Execute(null, ACommandType, ACommandText, AParams);
		}

		/// <summary>
		/// Executes the given text command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for execution.</param>
		/// <param name="AStatement">The text of the command to be executed.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The number of rows affected by the command.</returns>
		public int Execute(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			return Execute(ATransaction, CommandType.Text, AStatement, AParams);
		}

		/// <summary>
		/// Executes the given text command.
		/// </summary>
		/// <param name="ACommandText">The text of the command to be executed.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>The number of rows affected by the command.</returns>
		public int Execute(string AStatement, params Param[] AParams)
		{
			return Execute(null, AStatement, AParams);
		}

		/// <summary>
		/// Executes the given stored procedure.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for execution.</param>
		/// <param name="AProcedureName">The name of the procedure to be executed.</param>
		/// <param name="AParams">The parameters to the procedure, if any.</param>
		/// <returns>The number of rows affected.</returns>
		public int ExecuteProcedure(TransactionContext ATransaction, string AProcedureName, params Param[] AParams)
		{
			return Execute(ATransaction, CommandType.StoredProcedure, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Executes the given stored procedure.
		/// </summary>
		/// <param name="AProcedureName">The name of the procedure to be executed.</param>
		/// <param name="AParams">The parameters to the procedure, if any.</param>
		/// <returns>The number of rows affected.</returns>
		public int ExecuteProcedure(string AProcedureName, params Param[] AParams)
		{
			return ExecuteProcedure(null, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Returns a DataTable describing the result schema of the given command.
		/// </summary>
		/// <param name="ACommandType">The type of the command to be described.</param>
		/// <param name="ACommandText">The text of the command to be described.</param>
		/// <param name="AGivenParams">The parameters to the command, if any.</param>
		/// <returns>A DataTable describing the result schema.</returns>
		public DataTable Describe(CommandType ACommandType, string ACommandText, params Param[] AGivenParams)
		{
			DataTable LReturn = null;
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = GetConnection())
					{
						using (var LCommand = CreateCommand(LWrapper.Connection, null, ACommandType, ACommandText, AGivenParams))
						{
							using (IDataReader LResult = LCommand.ExecuteReader(CommandBehavior.SchemaOnly | CommandBehavior.KeyInfo))
							{
								LReturn = LResult.GetSchemaTable();
							}
						}
					}
				}
			);
			return LReturn;
		}

		/// <summary>
		/// Returns a DataTable describing the result schema of the given statement.
		/// </summary>
		/// <param name="AStatement">The text of the command to be described.</param>
		/// <param name="AGivenParams">The parameters to the command, if any.</param>
		/// <returns>A DataTable describing the result schema.</returns>
		public DataTable DescribeStatement(string AStatement, params Param[] AGivenParams)
		{
			return Describe(CommandType.Text, AStatement, AGivenParams);
		}
		
		/// <summary>
		/// Returns a DataTable describing the result schema of the given procedure.
		/// </summary>
		/// <param name="AProcedureName">The name of the stored procedure to be described.</param>
		/// <returns>A DataTable describing the result schema.</returns>
		public DataTable DescribeProcedure(string AProcedureName)
		{
			return Describe(CommandType.StoredProcedure, AProcedureName, null);
		}
		
		/// <summary>
		/// Opens a data reader on the given command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ACommandType">The type of the command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader Open(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			IDataReader LResult = null;
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = ATransaction == null ? GetConnection(false) : new ConnectionWrapper(ATransaction.Connection, false))
					{
						using (var LCommand = CreateCommand(LWrapper.Connection, ATransaction, ACommandType, ACommandText, AParams))
						{
							LResult = LCommand.ExecuteReader((ATransaction != null || Transaction.Current != null) ? CommandBehavior.Default : CommandBehavior.CloseConnection);
							try
							{
								SetOutputParameters(LCommand, AParams);
							}
							catch
							{
								LResult.Dispose();
								throw;
							}
						}
					}
				}
			);
			return LResult;
		}
		
		/// <summary>
		/// Opens a data reader on the given command.
		/// </summary>
		/// <param name="ACommandType">The type of the command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader Open(CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			return Open(null, ACommandType, ACommandText, AParams);
		}
		
		/// <summary>
		/// Opens a data reader on the given text command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader Open(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			return Open(ATransaction, CommandType.Text, AStatement, AParams);
		}
		
		/// <summary>
		/// Opens a data reader on the given text command.
		/// </summary>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader Open(string AStatement, params Param[] AParams)
		{
			return Open(null, AStatement, AParams);
		}

		/// <summary>
		/// Opens a data reader on the given stored procedure.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader OpenFromProcedure(TransactionContext ATransaction, string AProcedureName, params Param[] AParams)
		{
			return Open(ATransaction, CommandType.StoredProcedure, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Opens a data reader on the given stored procedure.
		/// </summary>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>An IDataReader opened on the result of the open command.</returns>
		public IDataReader OpenFromProcedure(string AProcedureName, params Param[] AParams)
		{
			return Open(null, CommandType.StoredProcedure, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Opens a data set with the results of the given command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ACommandType">The type of the command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>A DataSet populated with the results of the command.</returns>
		public DataSet OpenDataSet(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			DataSet LResult = null;
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = ATransaction == null ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
					{
						var LAdapter = ProviderFactory.CreateDataAdapter();
						LAdapter.SelectCommand = CreateCommand(LWrapper.Connection, ATransaction, ACommandType, ACommandText, AParams);
						LResult = new DataSet();
						LAdapter.Fill(LResult);
						SetOutputParameters(LAdapter.SelectCommand, AParams);
					}
				}
			);
			return LResult;
		}
		
		/// <summary>
		/// Opens a data set with the results of the given command.
		/// </summary>
		/// <param name="ACommandType">The type of the command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>A DataSet populated with the results of the command.</returns>
		public DataSet OpenDataSet(CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			return OpenDataSet(null, ACommandType, ACommandText, AParams);
		}
		
		/// <summary>
		/// Opens a data set with the results of the given text command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>A DataSet populated with the results of the command.</returns>
		public DataSet OpenDataSet(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			return OpenDataSet(ATransaction, CommandType.Text, AStatement, AParams);
		}
		
		/// <summary>
		/// Opens a data set with the results of the given text command.
		/// </summary>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		/// <returns>A DataSet populated with the results of the command.</returns>
		public DataSet OpenDataSet(string AStatement, params Param[] AParams)
		{
			return OpenDataSet(null, AStatement, AParams);
		}
		
		/// <summary>
		/// Opens a data set with the results of the given stored procedure.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AProcedureName">The name of the stord procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		/// <returns>A DataSet populated with the results of the stored procedure.</returns>
		public DataSet OpenDataSetFromProcedure(TransactionContext ATransaction, string AProcedureName, params Param[] AParams)
		{
			return OpenDataSet(ATransaction, CommandType.StoredProcedure, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Opens a data set with the results of the given stored procedure.
		/// </summary>
		/// <param name="AProcedureName">The name of the stord procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		/// <returns>A DataSet populated with the results of the stored procedure.</returns>
		public DataSet OpenDataSetFromProcedure(string AProcedureName, params Param[] AParams)
		{
			return OpenDataSetFromProcedure(null, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		public void FillDataSet(TransactionContext ATransaction, DataSet ADataSet, string ATableName, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			HandleRetry
			(
				() =>
				{
					using (var LWrapper = ATransaction == null ? GetConnection() : new ConnectionWrapper(ATransaction.Connection, false))
					{
						var LAdapter = ProviderFactory.CreateDataAdapter();
						LAdapter.SelectCommand = CreateCommand(LWrapper.Connection, ATransaction, ACommandType, ACommandText, AParams);
						LAdapter.Fill(ADataSet, ATableName);
						SetOutputParameters(LAdapter.SelectCommand, AParams);
					}
				}
			);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given command.
		/// </summary>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the command, if any.</param>
		public void FillDataSet(DataSet ADataSet, string ATableName, CommandType ACommandType, string ACommandText, params Param[] AParams)
		{
			FillDataSet(null, ADataSet, ATableName, ACommandType, ACommandText, AParams);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given statement.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="AStatement">The statement to be opened.</param>
		/// <param name="AParams">A list of parameters to the statement, if any.</param>
		public void FillDataSetFromStatement(TransactionContext ATransaction, DataSet ADataSet, string ATableName, string AStatement, params Param[] AParams)
		{
			FillDataSet(ATransaction, ADataSet, ATableName, CommandType.Text, AStatement, AParams);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given statement.
		/// </summary>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="AStatement">The statement to be opened.</param>
		/// <param name="AParams">A list of parameters to the statement, if any.</param>
		public void FillDataSetFromStatement(DataSet ADataSet, string ATableName, string AStatement, params Param[] AParams)
		{
			FillDataSetFromStatement(null, ADataSet, ATableName, AStatement, AParams);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given stiored procedure.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		public void FillDataSetFromProcedure(TransactionContext ATransaction, DataSet ADataSet, string ATableName, string AProcedureName, params Param[] AParams)
		{
			FillDataSet(ATransaction, ADataSet, ATableName, CommandType.StoredProcedure, AProcedureName, AParams);
		}
		
		/// <summary>
		/// Fills a dataset with the results of the given stiored procedure.
		/// </summary>
		/// <param name="ADataSet">The dataset to be populated.</param>
		/// <param name="ATableName">The name of the DataTable within the DataSet to be populated.</param>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		public void FillDataSetFromProcedure(DataSet ADataSet, string ATableName, string AProcedureName, params Param[] AParams)
		{
			FillDataSetFromProcedure(null, ADataSet, ATableName, AProcedureName, AParams);
		}

		/// <summary>
		/// Opens a DataTable populated with the results of the given command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given command.</returns>
		public DataTable OpenDataTable(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams) 
		{
			return OpenDataSet(ATransaction, ACommandType, ACommandText, AParams).Tables[0];
		}

		/// <summary>
		/// Opens a DataTable populated with the results of the given command.
		/// </summary>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given command.</returns>
		public DataTable OpenDataTable(CommandType ACommandType, string ACommandText, params Param[] AParams) 
		{
			return OpenDataSet(ACommandType, ACommandText, AParams).Tables[0];
		}

		/// <summary>
		/// Opens a DataTable populated with the results of the given text command.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given command.</returns>
		public DataTable OpenDataTable(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			return OpenDataSet(ATransaction, AStatement, AParams).Tables[0];
		}
		
		/// <summary>
		/// Opens a DataTable populated with the results of the given text command.
		/// </summary>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given command.</returns>
		public DataTable OpenDataTable(string AStatement, params Param[] AParams)
		{
			return OpenDataSet(AStatement, AParams).Tables[0];
		}
		
		/// <summary>
		/// Opens a DataTable populated with the results of the given stored procedure.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given stored procedure.</returns>
		public DataTable OpenDataTableFromProcedure(TransactionContext ATransaction, string AProcedureName, params Param[] AParams)
		{
			return OpenDataSetFromProcedure(ATransaction, AProcedureName, AParams).Tables[0];
		}
		
		/// <summary>
		/// Opens a DataTable populated with the results of the given stored procedure.
		/// </summary>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataTable populated with the results of the given stored procedure.</returns>
		public DataTable OpenDataTableFromProcedure(string AProcedureName, params Param[] AParams)
		{
			return OpenDataSetFromProcedure(AProcedureName, AParams).Tables[0];
		}
		
		/// <summary>
		/// Opens the first DataRow from the given command, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the command.</returns>
		public DataRow OpenDataRow(TransactionContext ATransaction, CommandType ACommandType, string ACommandText, params Param[] AParams) 
		{
			var rows = OpenDataSet(ATransaction, ACommandType, ACommandText, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}

		/// <summary>
		/// Opens the first DataRow from the given command, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="ACommandType">The type of command to be opened.</param>
		/// <param name="ACommandText">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the command.</returns>
		public DataRow OpenDataRow(CommandType ACommandType, string ACommandText, params Param[] AParams) 
		{
			var rows = OpenDataSet(ACommandType, ACommandText, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}

		/// <summary>
		/// Opens the first DataRow from the given text command, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the command.</returns>
		public DataRow OpenDataRow(TransactionContext ATransaction, string AStatement, params Param[] AParams)
		{
			var rows = OpenDataSet(ATransaction, AStatement, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}
		
		/// <summary>
		/// Opens the first DataRow from the given text command, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="AStatement">The text of the command to be opened.</param>
		/// <param name="AParams">A list of parameters to the open, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the command.</returns>
		public DataRow OpenDataRow(string AStatement, params Param[] AParams)
		{
			var rows = OpenDataSet(AStatement, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}
		
		/// <summary>
		/// Opens the first DataRow from the given stored procedure, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="ATransaction">The transaction context to use for the open.</param>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the stored procedure.</returns>
		public DataRow OpenDataRowFromProcedure(TransactionContext ATransaction, string AProcedureName, params Param[] AParams)
		{
			var rows = OpenDataSetFromProcedure(ATransaction, AProcedureName, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}
		
		/// <summary>
		/// Opens the first DataRow from the given stored procedure, or returns null if no rows were retrieved.
		/// </summary>
		/// <param name="AProcedureName">The name of the stored procedure to be opened.</param>
		/// <param name="AParams">A list of parameters to the stored procedure, if any.</param>
		/// <returns>A DataRow populated with the first row of the results of the stored procedure.</returns>
		public DataRow OpenDataRowFromProcedure(string AProcedureName, params Param[] AParams)
		{
			var rows = OpenDataSetFromProcedure(AProcedureName, AParams).Tables[0].Rows;
			return rows.Count > 0 ? rows[0] : null;
		}
	}
}
